﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;

namespace MasMembers.DAL
{
   public  class GroupsNamesEntity
    {
        public Int32 G_Id { get; set; }
        public String G_Name { get; set; }
        public String G_Supervisor { get; set; }
    }
    class GroupsNames
    {

        private static string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='MasMembers.accdb';Jet OLEDB:Database Password=Yarabe11";
        private static String cmdText = "";

        public static bool Add(GroupsNamesEntity s)
        {
            bool flag = true;
                       cmdText = "Insert into GroupsNames (G_Name,G_Supervisor) values (?,?)";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);


            cmd.Parameters.AddWithValue("@G_Name", s.G_Name);
            cmd.Parameters.AddWithValue("@G_Supervisor", s.G_Supervisor);
           
            try
            {
                Con.Open();
                int Num = cmd.ExecuteNonQuery();
                if (Num == 0)
                    flag = false;
            }

            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى ادخال البيانات" + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;


        }

        public static bool Update(GroupsNamesEntity s)
        {
            bool flag = true;  
            cmdText = "Update GroupsNames set  G_Name =?, G_Supervisor=?  WHERE Id= ? ";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);
            cmd.Parameters.AddWithValue("@G_Name", s.G_Name);
            cmd.Parameters.AddWithValue("@G_Supervisor", s.G_Supervisor);
            cmd.Parameters.AddWithValue("@G_Id", s.G_Id);

            try
            {
                Con.Open();
                int num = cmd.ExecuteNonQuery();
                if (num == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى تغيير البيانات " + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;

        }

        public static bool Delete(Int32 s)
        {
            bool flag = true;

            cmdText = "Delete from GroupsNames where Id = ?";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);

            cmd.Parameters.AddWithValue("@Id", s);

            try
            {
                Con.Open();
                int num = cmd.ExecuteNonQuery();
                if (num == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ اثناء الحذف ", "رساله ادارية " + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;
        }

        public static GroupsNamesEntity GetGroupByG_Name(String name)
        {
            {
                GroupsNamesEntity x = new GroupsNamesEntity();
                List<GroupsNamesEntity> GroupList = new List<GroupsNamesEntity>();

                cmdText = "Select * from GroupsNames where G_Name= ? ";

                OleDbConnection Con = new OleDbConnection(conString);
                OleDbCommand cmd = new OleDbCommand(cmdText, Con);

                cmd.Parameters.AddWithValue("@Id", name);

                try
                {
                    Con.Open();

                    OleDbDataReader reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        x = FillEntityFromReader(reader);
                    }

                    reader.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
                }
                finally
                {
                    if (Con.State == ConnectionState.Open)
                        Con.Close();
                }

                return x;
            }
        }
     
   
        public static GroupsNamesEntity GetGroupByG_ID(Int32 Id)
        {
            {
                GroupsNamesEntity x = null;
                List<GroupsNamesEntity> GroupList = new List<GroupsNamesEntity>();

                cmdText = "Select * from GroupsNames where Id= ? ";

                OleDbConnection Con = new OleDbConnection(conString);
                OleDbCommand cmd = new OleDbCommand(cmdText, Con);

                cmd.Parameters.AddWithValue("@Id", Id);

                try
                {
                    Con.Open();

                    OleDbDataReader reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        x = FillEntityFromReader(reader);
                    }

                    reader.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
                }
                finally
                {
                    if (Con.State == ConnectionState.Open)
                        Con.Close();
                }

                return x;
            }
        }
     
   

        public static GroupsNamesEntity  GetGroupID(GroupsNamesEntity  e)
        {
           
            cmdText = "Select * from Courses  where G_Name=?,  G_Supervisor=?";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);
                      cmd.Parameters.AddWithValue("@G_Name", e.G_Name);
            cmd.Parameters.AddWithValue("@G_Supervisor", e.G_Supervisor);
            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    e.G_Id = Int16.Parse(reader["Id"].ToString());
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(" خطأ عند تحديد رقم العميل مطلوبة من قاعدة بيانات العميل ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
               
            }

            return e; 
                
                
        }
     
        public static List<GroupsNamesEntity > GetAllGroups()
        {
            {
                GroupsNamesEntity x = null;
                List<GroupsNamesEntity> GroupList = new List<GroupsNamesEntity>();

                cmdText = "Select * from GroupsNames   ";

                OleDbConnection Con = new OleDbConnection(conString);
                OleDbCommand cmd = new OleDbCommand(cmdText, Con);



                try
                {
                    Con.Open();

                    OleDbDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        x = FillEntityFromReader(reader);
                        GroupList.Add(x);
                    }

                    reader.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("خطأ عند تحديد كل البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
                }
                finally
                {
                    if (Con.State == ConnectionState.Open)
                        Con.Close();
                }

                return GroupList;
            }
        }

        private static GroupsNamesEntity FillEntityFromReader(OleDbDataReader reader)
        {
           
 
            GroupsNamesEntity  x = new GroupsNamesEntity();
            x.G_Id = Int32.Parse(reader["Id"].ToString());
            x.G_Name = reader["G_Name"].ToString();
            x.G_Supervisor =reader["G_Supervisor"].ToString();
            
            return x;
        }
    }
    }

